#include "catch.hpp"
#include "common/file_system.hpp"
#include "dbgen.hpp"
#include "test_helpers.hpp"

using namespace duckdb;
using namespace std;

TEST_CASE("Test LEFT OUTER JOIN", "[join]") {
	unique_ptr<QueryResult> result;
	DuckDB db(nullptr);
	Connection con(db);
	con.EnableQueryVerification();

	REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER, j INTEGER)"));
	REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1, 2), (2, 3), (3, 4)"));
	REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers2(k INTEGER, l INTEGER)"));
	REQUIRE_NO_FAIL(con.Query("INSERT INTO integers2 VALUES (1, 10), (2, 20)"));

	result = con.Query("SELECT * FROM integers LEFT OUTER JOIN integers2 ON "
	                   "integers.i=integers2.k ORDER BY i");
	REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
	REQUIRE(CHECK_COLUMN(result, 1, {2, 3, 4}));
	REQUIRE(CHECK_COLUMN(result, 2, {1, 2, Value()}));
	REQUIRE(CHECK_COLUMN(result, 3, {10, 20, Value()}));

	// WHERE happens AFTER the join, thus [where k IS NOT NULL] filters out any tuples with generated NULL values from
	// the LEFT OUTER JOIN. Because of this, this join is equivalent to an inner join.
	result = con.Query("SELECT * FROM integers LEFT OUTER JOIN integers2 ON "
	                   "integers.i=integers2.k  WHERE k IS NOT NULL ORDER BY i");
	REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
	REQUIRE(CHECK_COLUMN(result, 1, {2, 3}));
	REQUIRE(CHECK_COLUMN(result, 2, {1, 2}));
	REQUIRE(CHECK_COLUMN(result, 3, {10, 20}));

	// however, any conditions in the ON clause happen BEFORE the join, thus the condition [integers2.k IS NOT NULL]
	// happens BEFORE any NULL values are generated by the LEFT OUTER JOIN.
	result = con.Query("SELECT * FROM integers LEFT OUTER JOIN integers2 ON "
	                   "integers.i=integers2.k AND integers2.k IS NOT NULL ORDER BY i");
	REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
	REQUIRE(CHECK_COLUMN(result, 1, {2, 3, 4}));
	REQUIRE(CHECK_COLUMN(result, 2, {1, 2, Value()}));
	REQUIRE(CHECK_COLUMN(result, 3, {10, 20, Value()}));

	// filter on LHS
	result = con.Query("SELECT * FROM integers LEFT OUTER JOIN integers2 ON i=1 ORDER BY i, k;");
	REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 2, 3}));
	REQUIRE(CHECK_COLUMN(result, 1, {2, 2, 3, 4}));
	REQUIRE(CHECK_COLUMN(result, 2, {1, 2, Value(), Value()}));
	REQUIRE(CHECK_COLUMN(result, 3, {10, 20, Value(), Value()}));

	// // left outer join on "true" is cross product
	result = con.Query("SELECT * FROM integers LEFT OUTER JOIN integers2 ON 1=1 ORDER BY i, k;");
	REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 2, 2, 3, 3}));
	REQUIRE(CHECK_COLUMN(result, 1, {2, 2, 3, 3, 4, 4}));
	REQUIRE(CHECK_COLUMN(result, 2, {1, 2, 1, 2, 1, 2}));
	REQUIRE(CHECK_COLUMN(result, 3, {10, 20, 10, 20, 10, 20}));

	// except if RHS is empty; then it is the LHS with NULl values appended
	result = con.Query(
	    "SELECT * FROM integers LEFT OUTER JOIN (SELECT * FROM integers2 WHERE 1<>1) tbl2 ON 1=2 ORDER BY i;");
	REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
	REQUIRE(CHECK_COLUMN(result, 1, {2, 3, 4}));
	REQUIRE(CHECK_COLUMN(result, 2, {Value(), Value(), Value()}));
	REQUIRE(CHECK_COLUMN(result, 3, {Value(), Value(), Value()}));

	// left outer join on "false" gives the LHS with the RHS filled as NULL
	result = con.Query("SELECT * FROM integers LEFT OUTER JOIN integers2 ON 1=2 ORDER BY i;");
	REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
	REQUIRE(CHECK_COLUMN(result, 1, {2, 3, 4}));
	REQUIRE(CHECK_COLUMN(result, 2, {Value(), Value(), Value()}));
	REQUIRE(CHECK_COLUMN(result, 3, {Value(), Value(), Value()}));

	// left outer join on NULL constant gives the LHS with the RHS filled as null as well
	result = con.Query("SELECT * FROM integers LEFT OUTER JOIN integers2 ON NULL<>NULL ORDER BY i;");
	REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
	REQUIRE(CHECK_COLUMN(result, 1, {2, 3, 4}));
	REQUIRE(CHECK_COLUMN(result, 2, {Value(), Value(), Value()}));
	REQUIRE(CHECK_COLUMN(result, 3, {Value(), Value(), Value()}));

	// left outer join on condition that only concerns the LHS
	result = con.Query("SELECT * FROM integers LEFT OUTER JOIN integers2 ON i=1 ORDER BY i, k;");
	REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 2, 3}));
	REQUIRE(CHECK_COLUMN(result, 1, {2, 2, 3, 4}));
	REQUIRE(CHECK_COLUMN(result, 2, {1, 2, Value(), Value()}));
	REQUIRE(CHECK_COLUMN(result, 3, {10, 20, Value(), Value()}));

	// // left outer join on condition that only concerns the RHS
	result = con.Query("SELECT * FROM integers LEFT OUTER JOIN integers2 ON l=20 ORDER BY i, k;");
	REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
	REQUIRE(CHECK_COLUMN(result, 1, {2, 3, 4}));
	REQUIRE(CHECK_COLUMN(result, 2, {2, 2, 2}));
	REQUIRE(CHECK_COLUMN(result, 3, {20, 20, 20}));

	result = con.Query("SELECT * FROM integers LEFT OUTER JOIN integers2 ON l>0 ORDER BY i, k;");
	REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 2, 2, 3, 3}));
	REQUIRE(CHECK_COLUMN(result, 1, {2, 2, 3, 3, 4, 4}));
	REQUIRE(CHECK_COLUMN(result, 2, {1, 2, 1, 2, 1, 2}));
	REQUIRE(CHECK_COLUMN(result, 3, {10, 20, 10, 20, 10, 20}));

	// // left outer join on condition that affects both, but is not a simple comparison
	result = con.Query("SELECT * FROM integers LEFT OUTER JOIN integers2 ON i=1 OR l=20 ORDER BY i, k;");
	REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 2, 3}));
	REQUIRE(CHECK_COLUMN(result, 1, {2, 2, 3, 4}));
	REQUIRE(CHECK_COLUMN(result, 2, {1, 2, 2, 2}));
	REQUIRE(CHECK_COLUMN(result, 3, {10, 20, 20, 20}));

	result = con.Query("SELECT * FROM integers LEFT OUTER JOIN integers2 ON i=4 OR l=17 ORDER BY i;");
	REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
	REQUIRE(CHECK_COLUMN(result, 1, {2, 3, 4}));
	REQUIRE(CHECK_COLUMN(result, 2, {Value(), Value(), Value()}));
	REQUIRE(CHECK_COLUMN(result, 3, {Value(), Value(), Value()}));

	result = con.Query("SELECT * FROM integers LEFT OUTER JOIN integers2 ON i+l=21 ORDER BY i;");
	REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
	REQUIRE(CHECK_COLUMN(result, 1, {2, 3, 4}));
	REQUIRE(CHECK_COLUMN(result, 2, {2, Value(), Value()}));
	REQUIRE(CHECK_COLUMN(result, 3, {20, Value(), Value()}));

	result = con.Query("SELECT * FROM integers LEFT OUTER JOIN integers2 ON i+l>12 ORDER BY i, k;");
	REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 3}));
	REQUIRE(CHECK_COLUMN(result, 1, {2, 3, 4, 4}));
	REQUIRE(CHECK_COLUMN(result, 2, {2, 2, 1, 2}));
	REQUIRE(CHECK_COLUMN(result, 3, {20, 20, 10, 20}));
}
